﻿using System;

using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Reflection;
using System.Data.SQLite;
using System.Data;
using System.Xml;
using log4net;
using log4net.Config;
using WABDCUI;

[assembly: log4net.Config.XmlConfigurator(Watch = true)]
[assembly: log4net.Config.Repository()]

namespace WABDCTools
{
    public static class Helper
    {
        private static string workingDir;
        private static IntStrComparer intStrComparer;

        public static string getWorkingDir()
        {
            if (workingDir == null)
            {
                //get working dir
                FileInfo currentAppFile = new FileInfo(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName);
                workingDir = currentAppFile.Directory.FullName;
            }
            return workingDir;
        }

        public static IntStrComparer getIntStrComparer()
        {
            if (intStrComparer == null)
            {
                intStrComparer = new IntStrComparer();
            }
            return intStrComparer;
        }

        public static string getBookDir()
        {
            return getWorkingDir() + "\\Books";
        }

        public static string getBookDirByName(string bookName)
        {
            return getBookDir() + "\\" + bookName;
        }

        public static string getDataDir()
        {
            return getWorkingDir() + "\\Data";
        }
    }

    public static class DBHelper
    {
        private static log4net.ILog logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        private const string DATABASE_NAME = @"WABDC.s3";
        private const string SQL_CREATE_TABLES = @"

            create table books (
                id integer primary key,
                name varchar(64)
            );

            create table lessons (
                id Integer primary key,
                book_id integer,
                name varchar(8),
                number_of_words integer,    --冗余字段，为了提高速度
                UNIQUE (book_id, name)
            );

            create table recites (
                id integer primary key,
                name varchar(64),
                create_time timestamp,      --记录创建时间
                access_time timestamp,      --最近访问时间
                access_count integer,       --访问次数
                number_of_words integer,    --冗余字段，为了提高速度
                UNIQUE (name)
            );

            create table recite_lesson (
                recite_id integer,
                lesson_id integer
            );

            create table words (
                id Integer primary key,
                lesson_id integer,
                spelling varchar(64),           --单词拼写
                pronunciation varchar(128),     --单词音标
                description varchar(512)        --单词释义
            );

            create table new_words (
                id Integer primary key,
                word_id integer,
                create_time timestamp,      --记录创建时间
                access_time timestamp,      --最近访问时间
                access_count integer        --访问次数
            );
            
        ";

        private static int update(SQLiteConnection conn, string sql)
        {
            SQLiteCommand cmdUpdate = new SQLiteCommand(conn);
            cmdUpdate.CommandText = sql;
            int count = cmdUpdate.ExecuteNonQuery();
            return count;
        }

        private delegate object GetQueryObject(SQLiteDataReader reader);

        private static object query(SQLiteConnection conn, string sql, GetQueryObject callback)
        {
            SQLiteCommand cmdQuery = new SQLiteCommand(conn);
            cmdQuery.CommandText = sql;
            SQLiteDataReader reader = cmdQuery.ExecuteReader();

            object r = callback.Invoke(reader);
            return r;
        }

        public static void generateDatabase()
        {
            string dataSource = Helper.getDataDir() + "\\" + DATABASE_NAME;
            FileInfo dbFile = new FileInfo(dataSource);
            if (dbFile.Exists)
            {
                dbFile.Delete();
            }
            SQLiteConnection.CreateFile(dataSource);
            SQLiteConnection conn = new SQLiteConnection(String.Format("data source={0}", dataSource));
            conn.Open();
            createNewDataSourceFile(conn);
            insertBooksRecords(conn);
            insertLessonsAndWords(conn);
            conn.Close();

            System.Windows.Forms.MessageBox.Show("完成！");
        }

        private static void createNewDataSourceFile(SQLiteConnection conn)
        {
            SQLiteCommand cmd = new SQLiteCommand(conn);

            cmd.CommandText = SQL_CREATE_TABLES;
            cmd.ExecuteNonQuery();
        }

        private static List<string> getBookList()
        {
            List<string> bookList = new List<string>();
            DirectoryInfo bookDir = new DirectoryInfo(Helper.getBookDir());
            if (bookDir.Exists)
            {

                DirectoryInfo[] dirs = bookDir.GetDirectories();
                foreach (DirectoryInfo dir in dirs)
                {
                    bookList.Add(dir.Name);
                }
            }
            return bookList;
        }

        private static void insertBooksRecords(SQLiteConnection conn)
        {
            string sql = "BEGIN;";
            int id = 0;
            List<string> bookList = getBookList();
            foreach (string bookName in bookList)
            {
                id++;
                sql += String.Format("insert into books values({0}, '{1}');", id, bookName);
            }
            sql += "COMMIT;";

            //run
            update(conn, sql);
        }

        private static List<string> getLessonsFromBook(string bookName)
        {
            List<string> lessons = new List<String>();
            DirectoryInfo bookDir = new DirectoryInfo(Helper.getBookDirByName(bookName));
            FileInfo[] files = bookDir.GetFiles();
            foreach (FileInfo file in files)
            {
                string name = file.Name;
                name = name.Substring(0, name.Length - 4);
                lessons.Add(name);
            }
            lessons.Sort(Helper.getIntStrComparer());
            return lessons;
        }

        private static List<Word> loadFromFile(string bookName, string lesson)
        {
            string path = Helper.getBookDirByName(bookName) + "\\" + lesson + ".xml";
            List<Word> words = new List<Word>();
            XmlTextReader xmlReader = new XmlTextReader(path);
            try
            {
                xmlReader.ReadStartElement("dict");
                while (xmlReader.Read())
                {
                    if ("dict".Equals(xmlReader.Name))
                    {
                        break;
                    }
                    Word w = new Word();
                    //read from xml
                    xmlReader.ReadStartElement("memo");
                    xmlReader.ReadStartElement("word");
                    w.Spelling = xmlReader.ReadString();
                    xmlReader.ReadEndElement();
                    xmlReader.ReadStartElement("pron");
                    w.Pronunciation = xmlReader.ReadString();
                    xmlReader.ReadEndElement();
                    xmlReader.ReadStartElement("description");
                    w.Description = xmlReader.ReadString();
                    xmlReader.ReadEndElement();
                    xmlReader.ReadEndElement();

                    words.Add(w);
                }
                return words;
            }
            catch (Exception e)
            {
                throw new Exception(e.ToString() + "\n" + path);
            }
            finally
            {
                xmlReader.Close();
            }
        }

        private static void insertLessonsAndWords(SQLiteConnection conn)
        {
            SQLiteCommand cmdQuery = new SQLiteCommand(conn);

            cmdQuery.CommandText = @"select id, name from books";
            SQLiteDataReader reader = cmdQuery.ExecuteReader();

            int lessonId = 0;
            int wordId = 0;
            StringBuilder sql = new StringBuilder();

            SQLiteTransaction transaction = conn.BeginTransaction();
            logger.Info("start ...");
            while (reader.Read())
            {
                int bookId = reader.GetInt32(0);
                string bookName = reader.GetString(1);
                List<string> lessons = getLessonsFromBook(bookName);
                foreach (string lesson in lessons)
                {
                    lessonId++;
                    List<Word> words = loadFromFile(bookName, lesson);
                    sql.Append("insert into lessons values(")
                            .Append(lessonId).Append(",")
                            .Append(bookId).Append(",")
                            .Append("'").Append(lesson).Append("',")
                            .Append(words.Count)
                            .Append(");\n");
                    update(conn, sql.ToString());
                    sql.Remove(0, sql.Length);

                    foreach (Word word in words)
                    {
                        wordId++;
                        sql.Append("insert into words values(")
                            .Append(wordId).Append(",")
                            .Append(lessonId).Append(",")
                            .Append("'").Append(word.Spelling.Replace("'", "''")).Append("',")
                            .Append("'").Append(word.Pronunciation.Replace("'", "''")).Append("',")
                            .Append("'").Append(word.Description.Replace("'", "''")).Append("'")
                            .Append(");\n");
                        update(conn, sql.ToString());
                        sql.Remove(0, sql.Length);
                    }
                }
            }
            transaction.Commit();
            logger.Info("over");
        }
    }
}
